﻿#if UNITY_EDITOR
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using UnityEditor;
using UnityEngine;
/// <summary>
/// XLSX逻辑代码
/// </summary>
public partial class ESF_Logic
{
    #region ResolveAllXlsxSchema 解析所有Xlsx表架构
    /// <summary>
    /// 解析所有Xlsx表架构
    /// </summary>
    /// <returns>true:解析成功,false:解析失败</returns>
    public static bool ResolveAllXlsxSchema()
    {
        List<ESF_CollectAsset_XLSX> nodes = CollectXlsxSchema<ESF_CollectAsset_XLSX>();
        bool result = true;
        List<string> errors = new List<string>();
        if (nodes != null && nodes.Count > 0)
        {
            float stepProgress = 1f / nodes.Count;
            for (int i = 0; i < nodes.Count; i++)
            {
                EditorUtility.DisplayProgressBar($"{nodes[i].name} Resolve Schema", $"【{i + 1}/{nodes.Count}】", Mathf.Clamp01(i * stepProgress));
                result &= ResolveXlsxSchema(nodes[i], (t, f, p) =>
                {
                    EditorUtility.DisplayProgressBar(t, f, Mathf.Clamp01(i * stepProgress) + p * stepProgress);
                });
            }
        }
        else
        {
            errors.Add($"【ResolveAllXlsxSchema】Can't found xlsx files");
            result &= false;
        }
        if (!result)
        {
            ESF_P.ESF_Error(errors.ESF_Join(enESF_SeparatorSymbol.Space));
        }
        else
        {
            ESF_P.ESF_Step($"ResolveAllXlsxSchema Success.");
        }
        ESF_Utility.ExecuteMenuAssetsRefresh();
        EditorUtility.ClearProgressBar();
        return result;
    }
    #endregion

    #region ResolveXlsxSchema 解析Xlsx表架构

    /// <summary>
    /// 解析Xlsx表架构
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    /// <param name="_isAll">是否是全部生成</param>
    /// <param name="_onDisplayProgressBar">进度条</param>
    /// <returns>true:解析成功,false:解析失败</returns>
    public static bool ResolveXlsxSchema(ESF_CollectAsset_XLSX _xlsx, Action<string, string, float> _onDisplayProgressBar = null)
    {
        bool result = true;
        if (_xlsx.isXLSX)
        {
            List<string> errors = new List<string>();
            ISheet sheet = ESF_Utility.GetSheet(_xlsx);
            List<ESF_Xml_XlsxTableColumnSchema> columns = new List<ESF_Xml_XlsxTableColumnSchema>();
            if (sheet != null)
            {
                Dictionary<string, ESF_Xml_XlsxTableColumnSchema> cacheColumns = new Dictionary<string, ESF_Xml_XlsxTableColumnSchema>();
                if (_xlsx.xlsxTableSchemaXmlConfig.xml.columns != null)
                {
                    foreach (var key in _xlsx.xlsxTableSchemaXmlConfig.xml.columns)
                    {
                        if (!cacheColumns.ContainsKey(key.csName))
                        {
                            cacheColumns.Add(key.csName, key);
                        }
                    }
                }
                ESF_Xml_XlsxTableColumnSchema tempCol = null;
                ESF_Xml_XlsxTableColumnSchema cacheCol = null;
                IRow csNameRow = sheet.GetRow(sheet.csName_RowIndex());
                IRow csDataTypeRow = sheet.GetRow(sheet.csDataType_RowIndex());
                IRow sqlNameRow = sheet.GetRow(sheet.sqlName_RowIndex());
                IRow descRow = sheet.GetRow(sheet.desc_RowIndex());
                ICell cellName = null;
                ICell cellType = null;
                ICell cell = null;
                if (HasAnyCell(csDataTypeRow))
                {
                    for (int ci = 0; ci <= csDataTypeRow.LastCellNum; ci++)
                    {
                        cellType = csDataTypeRow.GetCell(ci);
                        cellName = csNameRow.GetCell(ci);
                        if (cellName != null && !string.IsNullOrEmpty(cellName.ToString().Trim()))
                        {//如果属性名称不为空
                            if (IsValidCSType(cellType))
                            {//是否是有效的CS类型值
                                tempCol = new ESF_Xml_XlsxTableColumnSchema();
                                tempCol.xlsxOrdinal = cellName.ColumnIndex;
                                tempCol.csDataType = GetCSDataType(cellType);
                                tempCol.csName = cellName.ToString().Trim();                                
                                cell = sqlNameRow.GetCell(ci);
                                if (cell != null)
                                {
                                    tempCol.sqlName = cell.ToString().Trim();
                                }
                                cell = descRow.GetCell(ci);
                                if (cell != null)
                                {
                                    tempCol.desc = cell.ToString().Trim();
                                }
                                if (cacheColumns.ContainsKey(tempCol.csName))
                                {
                                    cacheCol = cacheColumns[tempCol.csName];
                                    tempCol.isDelete = cacheCol.isDelete;
                                    tempCol.canModify = cacheCol.canModify;
                                    tempCol.isPK = cacheCol.isPK;
                                    tempCol.isIgnore = cacheCol.isIgnore;
                                }
                                //当字段类型与autoIgnoreTypeName一致时，则自动设置为忽略字段
                                tempCol.isIgnore |= cellType.ToString().ToLower().Trim().Equals(xlsxAutoBuildFolder.xml.autoIgnoreTypeName.ToLower().Trim());
                                columns.Add(tempCol);
                                _onDisplayProgressBar?.Invoke($"{_xlsx.name} Resolver Schema", $"Cell： [{csDataTypeRow.xlsxSerialNumber()},{cellName.xlsxSerialNumber()}_{cellName.xlsxSerialNumber().XlsxColumnOrdinalToLetter()}] [{ci + 1}/{csDataTypeRow.LastCellNum}]", (float)(ci + 1) / csDataTypeRow.LastCellNum);
                            }
                            else if (cellType != null)
                            {
                                errors.Add($"ResolveXlsxSchema【{_xlsx.path.ESF_ApplyStepColor()}】 Cell【{(cellType.Row.xlsxSerialNumber() + "," + cellType.xlsxSerialNumber().XlsxColumnOrdinalToLetter()).ESF_ApplyStepColor()}】's value [{cellType.ESF_ApplyStepColor()}] is not valid cs type");
                                result &= false;
                            }
                        }
                    }
                }
                else
                {
                    errors.Add($"ResolveXlsxSchema【{_xlsx.path.ESF_ApplyStepColor()}】 Row【{sheet.csDataType_RowIndex().ESF_ApplyStepColor()}】 has no cell setting");
                    result &= false;
                }
            }
            else
            {
                errors.Add($"ResolveXlsxSchema【{_xlsx.path.ESF_ApplyStepColor()}】 can't found any Sheet");
                result &= false;
            }
            if (result)
            {
                _xlsx.xlsxTableSchemaXmlConfig.xml.columns = columns.ToArray();
                _xlsx.xlsxTableSchemaXmlConfig.SaveAsset();
                OnResultTip(_xlsx, "ResolveXlsxSchema", result);
            }
            else
            {
                ESF_P.ESF_Error(errors.ESF_Join(enESF_SeparatorSymbol.Space));
            }
        }
        return result;
    }
    #endregion

    #region BuildAllXlsxScript 生成所有Xlsx表脚本
    /// <summary>
    /// 生成所有Xlsx表脚本
    /// </summary>
    /// <returns>true:生成成功,false:生成失败</returns>
    public static bool BuildAllXlsxScript()
    {
        bool result = BuildLanguageScript();
        List<ESF_CollectAsset_XLSX> nodes = CollectXlsxSchema<ESF_CollectAsset_XLSX>();
        List<string> errors = new List<string>();
        List<ESF_Xml_XlsxTable_ScriptBuildSetting> sbcs = new List<ESF_Xml_XlsxTable_ScriptBuildSetting>();
        if (nodes != null && nodes.Count > 0)
        {
            ESF_Utility.DeleteFolder(xlsxAutoBuildFolder.xml.xlsxScriptEntityFolder);
            ESF_Utility.DeleteFolder(xlsxAutoBuildFolder.xml.xlsxScriptHelperFolder);
            float stepProgress = 1f / nodes.Count;
            for (int i = 0; i < nodes.Count; i++)
            {
                EditorUtility.DisplayProgressBar($"{nodes[i].name} Build Script", $"【{i + 1}/{nodes.Count}】", Mathf.Clamp01(i * stepProgress));
                result &= BuildXlsxScript(nodes[i], (t, f, p) =>
                {
                    EditorUtility.DisplayProgressBar(t, f, Mathf.Clamp01(i * stepProgress) + p * stepProgress);
                });
                sbcs.Add(nodes[i].xlsxScriptBuildSettingConfig.xml);
            }
        }
        else
        {
            errors.Add($"【BuildAllXlsxScript】Can't found xlsx files");
            result &= false;
        }
        ESF_BuildTextAsset bta = new ESF_BuildTextAsset("", "", enESF_UnityFileExt.CS, "");
        string code = string.Empty;
        #region 生成Select脚本 XlsxDbLibraryHelper_Select
        code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_XlsxDbLibraryHelper_Select_Script_txt);
        code = code.CompilerCSharp(new
        {
            sbcs,
            abf = xlsxAutoBuildFolder.xml,
            className = xlsxAutoBuildFolder.xml.xlsxScriptHelperClassName
        });
        bta.SetDirectory(Path.GetDirectoryName(xlsxAutoBuildFolder.xml.xlsxScriptHelperSelectFilePath));
        bta.SetFileName(Path.GetFileNameWithoutExtension(xlsxAutoBuildFolder.xml.xlsxScriptHelperSelectFilePath));
        bta.SetText(code);
        bta.CreateAsset();
        #endregion

        #region 生成Select脚本 XlsxDbLibraryHelper_SizeOf
        Dictionary<ESF_enCS2UnityDataType, ESF_CSharpCodeAttribute> enums =
            typeof(ESF_enCS2UnityDataType).ESF_EnumToSpecialValue<ESF_enCS2UnityDataType, ESF_CSharpCodeAttribute, ESF_CSharpCodeAttribute>((e, a) => { return a; });
        Dictionary<string, string> sizeofs = new Dictionary<string, string>();
        string sizeofType = string.Empty;
        bool isHashCode = false;
        bool isBool = false;
        foreach (var key in enums)
        {
            sizeofType = OnResolverSizeOfType(key.Value, out isHashCode, out isBool);
            if (!sizeofs.ContainsKey(key.Value.unityType))
            {
                sizeofs.Add(key.Value.unityType.ToLower(), sizeofType);
            }
        }
        code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_XlsxDbLibraryHelper_SizeOf_Script_txt);
        code = code.CompilerCSharp(new
        {
            sizeofs,
            abf = xlsxAutoBuildFolder.xml,
            className = xlsxAutoBuildFolder.xml.xlsxScriptHelperClassName
        });
        bta.SetDirectory(Path.GetDirectoryName(xlsxAutoBuildFolder.xml.xlsxScriptHelperSizeofFilePath));
        bta.SetFileName(Path.GetFileNameWithoutExtension(xlsxAutoBuildFolder.xml.xlsxScriptHelperSizeofFilePath));
        bta.SetText(code);
        bta.CreateAsset();
        #endregion
    
        if (!result)
        {
            ESF_P.ESF_Error(errors.ESF_Join(enESF_SeparatorSymbol.Space));
        }
        else
        {
            ESF_P.ESF_Step($"BuildAllXlsxScript Success.");
        }
        ESF_Utility.ExecuteMenuAssetsRefresh();
        EditorUtility.ClearProgressBar();
        return result;
    }
    #endregion

    #region BuildXlsxScript 生成Xlsx表脚本
    /// <summary>
    /// 生成Xlsx表脚本
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    /// <param name="_onDisplayProgressBar">进度条</param>
    /// <returns>true:生成成功,false:生成失败</returns>
    public static bool BuildXlsxScript(ESF_CollectAsset_XLSX _xlsx, Action<string, string, float> _onDisplayProgressBar = null)
    {
        bool result = ResolveXlsxSchema(_xlsx);
        if (_xlsx.isXLSX)
        {
            List<string> errors = new List<string>();
            List<ESF_Xml_XlsxTableColumn_ScriptBuildSetting> columns = new List<ESF_Xml_XlsxTableColumn_ScriptBuildSetting>();
            ESF_Xml_XlsxTableColumn_ScriptBuildSetting tempCol = null;
            ESF_CSharpCodeAttribute tempAttr = null;
            Dictionary<string, ESF_Xml_XlsxTable_DbValueType_ScriptBuildSetting> dbValueType = new Dictionary<string, ESF_Xml_XlsxTable_DbValueType_ScriptBuildSetting>();
            switch (_xlsx.xlsxTableSchemaXmlConfig.xml.xlsxTableClassify)
            {
                case ESF_enXlsxTableClassify.Determinant:
                    #region 行列式表
                    ISheet sheet = ESF_Utility.GetSheet(_xlsx);
                    if (sheet != null)
                    {//如果表不为空，则收集行列式表属性设置
                        ICell cell = null;
                        IRow row = null;
                        int rownum = sheet.LastRowNum;
                        int cellOrdinal = 0;
                        bool isVerify = false;
                        ESF_enCS2UnityDataType csType = ESF_enCS2UnityDataType.String;
                        for (int ri = sheet.startValue_RowIndex(); ri <= rownum; ri++)
                        {
                            tempCol = new ESF_Xml_XlsxTableColumn_ScriptBuildSetting();
                            row = sheet.GetRow(ri);
                            //检验名称
                            cellOrdinal = row.csName_Det_CellOrdinal();
                            cell = row.GetCell(cellOrdinal);
                            isVerify = cell != null;
                            if (isVerify)
                            {
                                tempCol.csName = cell.ToString();
                                tempCol.csFormalName = $"_{tempCol.csName}";
                                //检验类型
                                cellOrdinal = row.csDataType_Det_CellOrdinal();
                                cell = row.GetCell(cellOrdinal);
                                isVerify = IsValidCSType(cell);
                                if (isVerify)
                                {//类型合法
                                    csType = GetCSDataType(cell);
                                    tempAttr = csType.ESF_FirstAttribute<ESF_CSharpCodeAttribute>();
                                    #region 设置列数据
                                    tempCol.csType = csType;
                                    tempCol.unityType = tempAttr.unityType;
                                    tempCol.dbType = tempCol.unityType.ToLower();
                                    cellOrdinal = row.desc_Det_CellOrdinal();
                                    cell = row.GetCell(cellOrdinal);
                                    if (cell != null)
                                    {
                                        tempCol.fieldSummary = cell.ToString().ESF_ToSummary();
                                        tempCol.formalSummary = cell.ToString().ESF_ClearCRLF();
                                    }
                                    tempCol.isPK = false;
                                    tempCol.isDelete = false;
                                    tempCol.canModify = false;
                                    tempCol.xlsxOrdinal = row.csName_Det_CellOrdinal();
                                    tempCol.xlsxRowIndex = ri;
                                    tempCol.sizeofType = OnResolverSizeOfType(tempAttr, out tempCol.isHashCode, out tempCol.isBool);
                                    if (!dbValueType.ContainsKey(tempCol.dbType))
                                    {
                                        dbValueType.Add(tempCol.dbType, new ESF_Xml_XlsxTable_DbValueType_ScriptBuildSetting() { dbType = tempCol.dbType, dbTypeCount = 0, unityType = tempCol.unityType, csType = GetCSDataType(tempCol.unityType) });
                                    }
                                    tempCol.dbOrdinal = dbValueType[tempCol.dbType].dbTypeCount;
                                    dbValueType[tempCol.dbType].dbTypeCount++;
                                    #endregion
                                    columns.Add(tempCol);
                                }
                                else
                                {
                                    if (cell != null)
                                    {
                                        ESF_P.ESF_Error($"{_xlsx.name}表=>[{row.xlsxSerialNumber()}，{cellOrdinal.XlsxColumnOrdinalToLetter()}]，[{cell.ToString()}] is not verify type.");
                                    }
                                    else
                                    {
                                        ESF_P.ESF_Error($"{_xlsx.name}表=>[{row.xlsxSerialNumber()}，{cellOrdinal.XlsxColumnOrdinalToLetter()}]，type is not null.");
                                    }
                                }
                            }
                            else
                            {
                                ESF_P.ESF_Error($"{_xlsx.name}表=>[{row.xlsxSerialNumber()}，{cellOrdinal.XlsxColumnOrdinalToLetter()}]，name can not null.");
                            }
                            cell = row.GetCell(row.csDataType_Det_CellOrdinal());
                            cell = row.GetCell(row.csValue_Det_CellOrdinal());
                            cell = row.GetCell(row.desc_Det_CellOrdinal());
                            GetCSDataType(cell);
                            _onDisplayProgressBar?.Invoke($"{_xlsx.name} Build Script", $"【{ri}/{rownum}】", ri / (float)rownum);
                        }
                    }
                    #endregion
                    break;
                case ESF_enXlsxTableClassify.Normal:
                    #region 普通表
                    if (_xlsx.xlsxTableSchemaXmlConfig.xml.columns != null)
                    {
                        int count = _xlsx.xlsxTableSchemaXmlConfig.xml.columns.Length;
                        float progress = 0;
                        foreach (var key in _xlsx.xlsxTableSchemaXmlConfig.xml.columns)
                        {
                            if (!key.isIgnore)
                            {
                                tempAttr = key.csDataType.ESF_FirstAttribute<ESF_CSharpCodeAttribute>();
                                tempCol = new ESF_Xml_XlsxTableColumn_ScriptBuildSetting();
                                #region 设置列数据
                                tempCol.csFormalName = $"_{key.csName}";
                                tempCol.csName = key.csName;
                                tempCol.csType = key.csDataType;
                                tempCol.unityType = tempAttr.unityType;
                                tempCol.dbType = tempCol.unityType.ToLower();
                                tempCol.fieldSummary = key.desc.ESF_ToSummary();
                                tempCol.formalSummary = key.desc.ESF_ClearCRLF();
                                tempCol.isPK = key.isPK;
                                tempCol.isDelete = key.isDelete;
                                tempCol.canModify = key.canModify;
                                tempCol.xlsxOrdinal = key.xlsxOrdinal;
                                tempCol.xlsxRowIndex = -1;
                                tempCol.sizeofType = OnResolverSizeOfType(tempAttr, out tempCol.isHashCode, out tempCol.isBool);
                                if (!dbValueType.ContainsKey(tempCol.dbType))
                                {
                                    dbValueType.Add(tempCol.dbType, new ESF_Xml_XlsxTable_DbValueType_ScriptBuildSetting() { dbType = tempCol.dbType, dbTypeCount = 0, unityType = tempCol.unityType, csType = GetCSDataType(tempCol.unityType) });
                                }
                                tempCol.dbOrdinal = dbValueType[tempCol.dbType].dbTypeCount;
                                dbValueType[tempCol.dbType].dbTypeCount++;
                                #endregion
                                columns.Add(tempCol);
                            }                            
                            progress++;
                            _onDisplayProgressBar?.Invoke($"{_xlsx.name} Build Script", $"【{progress}/{count}】", progress / count);
                        }
                    }
                    #endregion
                    break;
            }
            if (result)
            {
                _xlsx.xlsxScriptBuildSettingConfig.xml.dbTypes = new List<ESF_Xml_XlsxTable_DbValueType_ScriptBuildSetting>(dbValueType.Values).ToArray();
                _xlsx.xlsxScriptBuildSettingConfig.xml.columns = columns.ToArray();
                _xlsx.xlsxScriptBuildSettingConfig.SaveAsset();
                OnCreateScript(_xlsx);
                OnResultTip(_xlsx, "BuildXlsxScript", result);
            }
            else
            {
                ESF_P.ESF_Error(errors.ESF_Join(enESF_SeparatorSymbol.Space));
            }
        }
        return result;
    }

    /// <summary>
    /// 解析sizeof类型
    /// </summary>
    /// <param name="_attr">属性</param>
    /// <param name="_isHashCode">是否hashCode值</param>
    /// <param name="_isBool">是否bool值</param>
    /// <returns>sizeof类型</returns>
    private static string OnResolverSizeOfType(ESF_CSharpCodeAttribute _attr, out bool _isHashCode, out bool _isBool)
    {
        string sizeOfType = string.Empty;
        _isHashCode = _attr.csTypeOf.Equals(typeof(string)) || _attr.csTypeOf.Equals(typeof(Vector2)) || _attr.csTypeOf.Equals(typeof(Vector3)) || _attr.csTypeOf.Equals(typeof(Vector4));
        _isBool = _attr.csTypeOf.Equals(typeof(bool));
        if (_isHashCode)
        {
            sizeOfType = ESF_enCS2UnityDataType.Int.ESF_FirstAttribute<ESF_CSharpCodeAttribute>().unityType;
        }
        else if (_isBool)
        {
            sizeOfType = ESF_enCS2UnityDataType.Byte.ESF_FirstAttribute<ESF_CSharpCodeAttribute>().unityType;
        }
        else
        {
            sizeOfType = _attr.unityType;
        }
        return sizeOfType;
    }

    /// <summary>
    /// 生成脚本
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    private static void OnCreateScript(ESF_CollectAsset_XLSX _xlsx)
    {
        string code = string.Empty;
        #region 创建实体类
        switch (_xlsx.xlsxTableSchemaXmlConfig.xml.xlsxTableClassify)
        {
            case ESF_enXlsxTableClassify.Determinant:
                code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_Entity_Det_Script_txt);
                break;
            case ESF_enXlsxTableClassify.Normal:
                if (_xlsx.xlsxScriptBuildSettingConfig.xml.hasPK)
                {
                    code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_Entity_PK_Script_txt);
                }
                else
                {
                    code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_Entity_NoPK_Script_txt);
                }
                break;
        }
        code = code.CompilerCSharp(new
        {
            sbc = _xlsx.xlsxScriptBuildSettingConfig.xml,
            abf = _xlsx.xlsxAutoBuildFolderXmlConfig,
            lanCN = languageSetting.xml.languageHelperScriptClassName
        });
        _xlsx.entityBuildTextAsset.SetText(code);
        if (File.Exists(_xlsx.entityBuildTextAsset.filePath))
        {
            File.Delete(_xlsx.entityBuildTextAsset.filePath);
        }
        _xlsx.entityBuildTextAsset.CreateAsset();
        #endregion

        ESF_BuildTextAsset bta = new ESF_BuildTextAsset("", "", enESF_UnityFileExt.CS, "");
        #region HelperSelect 类
        code = string.Empty;
        switch (_xlsx.xlsxTableSchemaXmlConfig.xml.xlsxTableClassify)
        {
            case ESF_enXlsxTableClassify.Determinant:
                code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_HelperSelect_Det_Script_txt);
                break;
            case ESF_enXlsxTableClassify.Normal:
                if (_xlsx.xlsxScriptBuildSettingConfig.xml.hasPK)
                {
                    code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_HelperSelect_PK_Script_txt);
                }
                else
                {
                    code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_HelperSelect_NoPK_Script_txt);
                }
                break;
        }
        code = code.CompilerCSharp(new
        {
            sbc = _xlsx.xlsxScriptBuildSettingConfig.xml,
            abf = _xlsx.xlsxAutoBuildFolderXmlConfig
        });
        bta.SetFileName(_xlsx.xlsxScriptBuildSettingConfig.xml.helperSelectFileName);
        bta.SetDirectory(_xlsx.xlsxAutoBuildFolderXmlConfig.xlsxScriptHelperFolder);
        bta.SetText(code);
        if (File.Exists(bta.filePath))
        {
            File.Delete(bta.filePath);
        }
        bta.CreateAsset();
        #endregion

        #region 扩展类
        code = string.Empty;
        code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_Entity_Extend_Scripte_txt);
        code = code.CompilerCSharp(new
        {
            sbc = _xlsx.xlsxScriptBuildSettingConfig.xml,
            abf = _xlsx.xlsxAutoBuildFolderXmlConfig
        });
        bta.SetFileName(_xlsx.xlsxScriptBuildSettingConfig.xml.extendFileName);
        bta.SetDirectory(_xlsx.xlsxAutoBuildFolderXmlConfig.xlsxScriptExtendFolder);
        bta.SetText(code);
        if (!File.Exists(bta.filePath))
        {
            bta.CreateAsset();
        }
        #endregion
    }
    #endregion

    #region BuildAllXlsxDbMaster 生成所有Xlsx表Db数据库
    /// <summary>
    /// 生成所有Xlsx表Db数据库
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    /// <returns>true:生成成功,false:生成失败</returns>
    public static bool BuildAllXlsxDbMaster()
    {
        bool result = true;        
        List<string> errors = new List<string>();
        ESF_Utility.DeleteFolder(xlsxAutoBuildFolder.xml.xlsxDbLibaray_TargetFolder);
        ESF_Utility.ExecuteMenuAssetsRefresh();        
        List<ESF_CollectAsset_XLSX> nodes = CollectXlsxSchema<ESF_CollectAsset_XLSX>();
        
        if (nodes != null && nodes.Count > 0)
        {
            AssetDatabase.StartAssetEditing();            
            XlsxDbLibrary xlsxDbLibrary = null;
            ESF_BuildEngineAsset bea = null;
            string xlsxDbLibraryFilePath = string.Empty;
            enESF_AllLanguageClassify defaultLan = 0;
            Dictionary<enESF_AllLanguageClassify, int> langs = GetLanguages(ref defaultLan);

            #region XlsxDbLibrary
            bea = new ESF_BuildEngineAsset(Path.GetFileNameWithoutExtension(xlsxAutoBuildFolder.xml.xlsxDbLibraryFilePath),
                Path.GetDirectoryName(xlsxAutoBuildFolder.xml.xlsxDbLibraryFilePath).ESF_TransPathSeparatorCharToUnityChar(),
                enESF_UnityFileExt.Asset, typeof(XlsxDbLibrary).FullName);
            bea.LoadAsset(true);
            xlsxDbLibraryFilePath = bea.filePath;
            xlsxDbLibrary = (XlsxDbLibrary)bea.engineAsset;
            #endregion

            #region XlsxDbMaster            
            xlsxDbLibrary.masters = new XlsxDbMaster[langs.Count];
            ESF_Utility.ExecuteMenuAssetsRefresh();
            bea = new ESF_BuildEngineAsset("", Path.Combine(xlsxAutoBuildFolder.xml.xlsxDbLibaray_TargetFolder, "DbMasters").ESF_TransPathSeparatorCharToUnityChar(), enESF_UnityFileExt.Asset, typeof(XlsxDbMaster).FullName);
            foreach (var key in langs)
            {
                bea.SetFileName($"{key.Key.ESF_FirstAttribute<ESF_FolderNameAttribute>().folderName}_Master");
                bea.LoadAsset(true);
                xlsxDbLibrary.masters[key.Value] = (XlsxDbMaster)bea.engineAsset;
                xlsxDbLibrary.masters[key.Value].tables = new XlsxDbTable[nodes.Count];
            }
            #endregion

            #region XlsxDbTable
            float stepProgress = 1f / nodes.Count;
            int masterIndex = 0;
            for (int i = 0; i < nodes.Count; i++)
            {
                EditorUtility.DisplayProgressBar($"{nodes[i].name} Build DbMaster", $"【{i + 1}/{nodes.Count}】", Mathf.Clamp01(i * stepProgress));
                result &= BuildXlsxDbMaster(nodes[i], (t, f, p) =>
                {
                    EditorUtility.DisplayProgressBar(t, f, Mathf.Clamp01(i * stepProgress) + p * stepProgress);
                });
                foreach (var key in nodes[i].dbTables)
                {
                    masterIndex = langs[(enESF_AllLanguageClassify)key.Key];
                    xlsxDbLibrary.masters[masterIndex].tables[nodes[i].xlsxScriptBuildSettingConfig.xml.tableIndex] = key.Value;
                }
            }
            #endregion

            foreach (var m in xlsxDbLibrary.masters)
            {
                
                foreach (var t in m.tables)
                {
                    if (t != null) {
                        EditorUtility.SetDirty(t);
                    }
                }
                EditorUtility.SetDirty(m);
            }
            EditorUtility.SetDirty(xlsxDbLibrary);
            AssetDatabase.StopAssetEditing();
            AssetDatabase.SaveAssets();
            ESF_Utility.ExecuteMenuAssetsRefresh();

            #region 生成HFDbHelper的Library路径

            var abb = ESF_Logic.assetBundleBuild.xml;
            abb.Resolve();

            string enRootName = string.Empty;
            string rootPath = string.Empty;
            string relativePath = string.Empty;
            string dbPath = $"{xlsxDbLibraryFilePath.ESF_TransPathSeparatorCharToUnityChar()}";
            abb.GetRootFolderEnumName(dbPath,out enRootName,out rootPath,out relativePath);
            ESF_BuildTextAsset bta = new ESF_BuildTextAsset(xlsxAutoBuildFolder.xml.xlsxScriptHelperClassName, xlsxAutoBuildFolder.xml.xlsxScriptRootFolder, enESF_UnityFileExt.CS, "");
            string code = string.Empty;
            code = File.ReadAllText(ESF_enResxTempleteFile.Compiler_Xlsx_XlsxDbLibraryHelper_Script_txt);
            code = code.CompilerCSharp(new
            {
                dbPath,
                enRootName,
                rootPath,
                relativePath,
                className = xlsxAutoBuildFolder.xml.xlsxScriptHelperClassName
            }).ReplaceSignESF2HF();
            bta.SetText(code);
            bta.CreateAsset();
            #endregion
        }
        else
        {
            errors.Add($"Can't found xlsx files");
            result &= false;
        }       
        if (!result)
        {
            ESF_P.ESF_Error(errors.ESF_Join(enESF_SeparatorSymbol.Space));
        }
        else
        {
            ESF_P.ESF_Step($"BuildAllXlsxDbMaster Success.");
        }
        ESF_Utility.ExecuteMenuAssetsRefresh();
        EditorUtility.ClearProgressBar();
        return result;
    }
    #endregion

    #region BuildXlsxDbMaster 生成Xlsx表Db数据库
    /// <summary>
    /// 生成Xlsx表Db数据库
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    /// <param name="_onDisplayProgressBar">进度条</param>
    /// <returns>true:生成成功,false:生成失败</returns>
    public static bool BuildXlsxDbMaster(ESF_CollectAsset_XLSX _xlsx, Action<string, string, float> _onDisplayProgressBar = null)
    {
        bool result = _xlsx.VerifyKey();
        if (_xlsx.isXLSX)
        {
            _xlsx.AutoCreateTableAsset();
            List<XlsxDbTableRow> dbRows = new List<XlsxDbTableRow>();
            XlsxDbTableRow dbRow = null;
            enESF_AllLanguageClassify defaultLan = xlsxAutoBuildFolder.xml.xlsxDefaultTableLanguage;
            List<int> useDefaultLangs = new List<int>();
            foreach (KeyValuePair<int, XlsxDbTable> key in _xlsx.dbTables)
            {
                dbRows.Clear();
                if (File.Exists(key.Value.tablePath))
                {
                    ISheet sheet = ESF_Utility.GetSheet(key.Value.tablePath, _xlsx.xlsxTableSchemaXmlConfig.xml.xlsxSheetIndex());
                    switch (_xlsx.xlsxTableSchemaXmlConfig.xml.xlsxTableClassify)
                    {
                        case ESF_enXlsxTableClassify.Determinant:
                            dbRow = OnSetDetTableRow(_xlsx, key.Value, sheet, (t, f, p) => { _onDisplayProgressBar?.Invoke(t, f, p); });
                            if (dbRow != null)
                            {
                                dbRows.Add(dbRow);
                            }
                            break;
                        case ESF_enXlsxTableClassify.Normal:
                            float stepProgress = 1f / sheet.LastRowNum;
                            for (int rowIndex = sheet.startValue_RowIndex(); rowIndex <= sheet.LastRowNum; rowIndex++)
                            {
                                dbRow = OnSetNormalTableRow(_xlsx, key.Value, sheet, rowIndex);
                                _onDisplayProgressBar?.Invoke($"{_xlsx.name} SetNormalTableRow", $"【{rowIndex}/{sheet.LastRowNum}】", rowIndex / (float)sheet.LastRowNum);
                                if (dbRow != null)
                                {
                                    dbRows.Add(dbRow);
                                }
                            }
                            break;
                    }
                    key.Value.rows = dbRows.ToArray();
                }
                else
                {
                    useDefaultLangs.Add(key.Key);
                }
            }

            int idx = (int)defaultLan;
            foreach (var key in useDefaultLangs)
            {
                if (_xlsx.dbTables[key] != null)
                {
                    AssetDatabase.DeleteAsset(AssetDatabase.GetAssetPath(_xlsx.dbTables[key]));
                }
                _xlsx.dbTables[key].tablePath = _xlsx.dbTables[idx].tablePath;
                _xlsx.dbTables[key].tableLanguage = _xlsx.dbTables[idx].tableLanguage;
                _xlsx.dbTables[key] = _xlsx.dbTables[idx];
            }
            OnResultTip(_xlsx, "BuildXlsxDbMaster", result);
        }
        return result;
    }

    /// <summary>
    /// 设置行列式表数据
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    /// <param name="_dbTable">db表</param>
    /// <param name="_sheet">Sheet表</param>
    /// <param name="_onDisplayProgressBar">进度条</param>
    /// <returns>新加入行数据</returns>
    private static XlsxDbTableRow OnSetDetTableRow(ESF_CollectAsset_XLSX _xlsx, XlsxDbTable _dbTable, ISheet _sheet, Action<string, string, float> _onDisplayProgressBar = null)
    {
        XlsxDbTableRow dbRow = null;
        bool result = _xlsx.xlsxScriptBuildSettingConfig.xml.columns != null && _xlsx.xlsxScriptBuildSettingConfig.xml.columns.Length > 0;
        if (result)
        {
            ICell cell = null;
            IRow row = null;
            string cellValue = string.Empty;
            string error = string.Empty;
            dbRow = _xlsx.CrerateNewRow();
            row = _sheet.GetRow(_sheet.startValue_RowIndex());
            dbRow.xlsxRowIndex = row.xlsxSerialNumber();
            dbRow.noPKUniqueId = dbRow.xlsxRowIndex << sizeof(int) + _dbTable.tableLanguage;
            float progress = 0;
            int count = _xlsx.xlsxScriptBuildSettingConfig.xml.columns.Length;
            foreach (var key in _xlsx.xlsxScriptBuildSettingConfig.xml.columns)
            {
                row = _sheet.GetRow(key.xlsxRowIndex);
                cell = row.GetCell(row.csName_Det_CellOrdinal());
                cellValue = cell == null ? string.Empty : cell.ToString().Trim();
                result = !string.IsNullOrEmpty(cellValue) && cellValue.Equals(key.csName);
                if (result)
                {
                    cell = row.GetCell(row.csValue_Det_CellOrdinal());
                    cellValue = cell == null ? string.Empty : cell.ToString().Trim();
                    result = !string.IsNullOrEmpty(cellValue);
                    _xlsx.SetColumnValue(key, cellValue, dbRow);
                }
                else
                {
                    error = $" [{row.xlsxSerialNumber()},{key.xlsxOrdinal.XlsxColumnOrdinalToLetter()}] is not {key.csName}";
                }
                if (!result)
                {
                    ESF_P.ESF_Error($"{error}=>{_dbTable.tablePath}");
                    break;
                }
                progress++;
                _onDisplayProgressBar?.Invoke($"{_xlsx.name} SetDetTableRow", $"【{progress}/{count}】", progress / count);
            }
            _xlsx.ResolverKey(dbRow);
        }
        return dbRow;
    }

    /// <summary>
    /// 设置普通表行数据
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    /// <param name="_dbTable">db表</param>
    /// <param name="_sheet">Sheet表</param>
    /// <param name="_sheetRowIndex">Sheet表行索引</param>
    /// <returns>新加入行数据</returns>
    private static XlsxDbTableRow OnSetNormalTableRow(ESF_CollectAsset_XLSX _xlsx, XlsxDbTable _dbTable, ISheet _sheet, int _sheetRowIndex)
    {
        XlsxDbTableRow dbRow = null;
        bool result = _xlsx.xlsxScriptBuildSettingConfig.xml.columns != null && _xlsx.xlsxScriptBuildSettingConfig.xml.columns.Length > 0;
        if (result)
        {
            ICell cell = null;
            IRow row = null;
            string cellValue = string.Empty;
            string error = string.Empty;
            int curRowIndex = 0;
            dbRow = _xlsx.CrerateNewRow();
            foreach (var key in _xlsx.xlsxScriptBuildSettingConfig.xml.columns)
            {
                row = _sheet.GetRow(curRowIndex);
                curRowIndex = _sheet.csName_RowIndex();
                cell = row.GetCell(key.xlsxOrdinal);
                cellValue = cell == null ? string.Empty : cell.ToString().Trim();
                result = !string.IsNullOrEmpty(cellValue) && cellValue.Equals(key.csName);
                if (result)
                {
                    row = _sheet.GetRow(_sheetRowIndex);
                    dbRow.xlsxRowIndex = row.xlsxSerialNumber();
                    dbRow.noPKUniqueId = dbRow.xlsxRowIndex << sizeof(int) + _dbTable.tableLanguage;
                    cell = row.GetCell(key.xlsxOrdinal);
                    cellValue = cell == null ? string.Empty : cell.ToString().Trim();
                    //除主键，其他列均可为空
                    result = !key.isPK || (key.isPK && !string.IsNullOrEmpty(cellValue));
                    if (result)
                    {
                        _xlsx.SetColumnValue(key, cellValue, dbRow);
                    }
                    else
                    {
                        error = $"PK cell {key.csName} [{row.xlsxSerialNumber()},{key.xlsxOrdinal.XlsxColumnOrdinalToLetter()}] is not null";
                    }
                }
                else
                {
                    error = $" [{row.xlsxSerialNumber()},{key.xlsxOrdinal.XlsxColumnOrdinalToLetter()}] is not {key.csName}";
                }
                if (!result)
                {
                    ESF_P.ESF_Log($"{error}=>{_dbTable.tablePath}".ESF_ApplyWarningColor());
                    break;
                }
            }
            if (_xlsx.IsDeleteRow(dbRow))
            {
                dbRow = null;
            }
            else
            {
                _xlsx.ResolverKey(dbRow);
            }
        }
        return dbRow;
    }
    #endregion

    #region OnResultTip 结果tip
    /// <summary>
    /// 结果tip
    /// </summary>
    /// <param name="_xlsx">xlsx表</param>
    /// <param name="_tip">tip</param>
    /// <param name="_result">结果</param>
    static void OnResultTip(ESF_CollectAsset_XLSX _xlsx, string _tip, bool _result)
    {
        string stateTip = _result ? "success" : "failed";
        ESF_P.ESF_Log($"{_tip} [{_xlsx.path}] {stateTip}.");
    }
    #endregion
}
#endif
